package com.better.util.excelUtil;

import com.alibaba.fastjson.JSONObject;
import com.google.gson.JsonObject;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
	private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	/**
	 * 创建excel
	 * @param topic 报表名称
	 * @param titles 标题列
	 * @param datas 报表数据
	 * @param isTotal 是否创建统计行
	 * @return
	 */
	public static <T> HSSFWorkbook createExcel(String topic, List<String> titles, List<T> datas, boolean isTotal) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle cellStyle=wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFSheet sheet = wb.createSheet(topic);
		sheet.setDefaultRowHeightInPoints(15);
		//创建名称
		HSSFRow row1 = sheet.createRow(0);
		HSSFCell cell = row1.createCell(0);
		sheet.setColumnWidth(cell.getColumnIndex(), 256 * 25);
		cell.setCellStyle(cellStyle);
		row1.setRowStyle(cellStyle);
		cell.setCellValue(topic);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.size()));
		//创建标题行
		createTitle(titles, cellStyle, sheet);

		if(null != datas && datas.size()>0){
			for (int i=0;i<datas.size();i++) {
				//创建合计行
				if(i==0 && isTotal){
					HSSFCellStyle cellStyle1=wb.createCellStyle();
					cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					cellStyle1.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
					HSSFRow row3 = sheet.createRow(2);
					row3.setRowStyle(cellStyle1);
					HSSFFont fontStyle = wb.createFont();
					fontStyle.setColor(HSSFColor.RED.index);
					cellStyle1.setFont(fontStyle);
					//序号
					HSSFCell cell3_0 = row3.createCell(0);
					cell3_0.setCellValue("");
					cell3_0.setCellStyle(cellStyle1);
					sheet.setColumnWidth(cell3_0.getColumnIndex(), 256 * 25);

					HSSFCell cell3_1 = row3.createCell(1);
					cell3_1.setCellValue("总计");
					cell3_1.setCellStyle(cellStyle1);
					sheet.setColumnWidth(cell3_1.getColumnIndex(), 256 * 25);
					List<Field> filds = ReflectionUtils.getParantField(datas.get(i));
					if(null != filds && filds.size()>0){
						for (int j=0;j<filds.size() && j<titles.size();j++) {
							Field field = filds.get(j);
							String typeName = field.getGenericType().getTypeName();
							logger.info("反射类型为："+typeName);
							HSSFCell cell1_0 = row3.createCell(j+2);
							cell1_0.setCellStyle(cellStyle1);
							sheet.setColumnWidth(cell1_0.getColumnIndex(), 256 * 25);
							cell1_0.setCellValue(ReflectionUtils.getFieldValue(datas.get(i), filds.get(j).getName()).toString());
						}
					}
					CreateRow(datas.get(i), cellStyle, sheet,i,isTotal,titles.size());
				}else{
					CreateRow(datas.get(i), cellStyle, sheet,i,isTotal,titles.size());
				}
			}
		}
		return wb;
	}
	/**
	 *创建标题行
	 * @param titles
	 * @param cellStyle
	 * @param sheet
	 */
	private static void createTitle(List<String> titles, HSSFCellStyle cellStyle, HSSFSheet sheet) {
		if(null != titles && titles.size()>0){
			HSSFRow row2 = sheet.createRow(1);
			row2.setRowStyle(cellStyle);
			HSSFCell cell1_0 = row2.createCell(0);
			sheet.setColumnWidth(cell1_0.getColumnIndex(), 256 * 25);
			cell1_0.setCellStyle(cellStyle);
			cell1_0.setCellValue("序号");
			for (int i=0;i<titles.size();i++) {
				HSSFCell cell1_1 = row2.createCell(i+1);
				sheet.setColumnWidth(cell1_1.getColumnIndex(), 256 * 25);
				cell1_1.setCellStyle(cellStyle);
				cell1_1.setCellValue(titles.get(i));
			}
		}
	}
	/**
	 * 创建行
	 * @param obj
	 * @param cellStyle
	 * @param sheet
	 * @param index
	 */
	private static void CreateRow(Object obj, HSSFCellStyle cellStyle, HSSFSheet sheet, int index, boolean isTotal, int titles) {
		List<Field> childFilds = ReflectionUtils.getObjectField(obj);
		if(null != childFilds && childFilds.size()>0){
			HSSFRow row4 = sheet.createRow(isTotal?3+index:2+index);
			row4.setRowStyle(cellStyle);
			HSSFCell cell4_0 = row4.createCell(0);
			cell4_0.setCellStyle(cellStyle);
			sheet.setColumnWidth(cell4_0.getColumnIndex(), 256 * 25);
			cell4_0.setCellValue(index+1);
			int k =0;
			for (int j = 0; j < childFilds.size() && j<titles; j++) {
				Field field = childFilds.get(j);
				boolean assignableFrom = field.getType().isAssignableFrom(List.class);
				if(!assignableFrom){
					HSSFCell cell4_1 = row4.createCell(k+1);
					cell4_1.setCellStyle(cellStyle);
					sheet.setColumnWidth(cell4_1.getColumnIndex(), 256 * 25);
					cell4_1.setCellValue(ReflectionUtils.getFieldValue(obj, childFilds.get(j).getName()));
					k++;
				}else{
					String fieldValue = ReflectionUtils.getFieldValue(obj, childFilds.get(j).getName());
					String subjectName = fieldValue.substring(1, fieldValue.lastIndexOf("]"));
					List<String> scores = Arrays.asList(subjectName.split(","));
					for (int i=0;i<scores.size();i++){
						HSSFCell cell4_2 = row4.createCell(k+1);
						cell4_2.setCellStyle(cellStyle);
						sheet.setColumnWidth(cell4_2.getColumnIndex(), 256 * 25);
						cell4_2.setCellValue(scores.get(i));
						k++;
					}
				}
			}
			logger.info("组装的每行参数{}"+row4);
		}
	}

	/**
	 * 下载文件
	 * @param path 下载路径
	 * @param response 输出流对象
	 */
	public static void download(String fileName, HSSFWorkbook wb, HttpServletResponse response) {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		BufferedInputStream bis = null;
		BufferedOutputStream bos = null;
		try {
			wb.write(os);
			byte[] content = os.toByteArray();
			InputStream is = new ByteArrayInputStream(content);
			ServletRequestAttributes request = (ServletRequestAttributes) RequestContextHolder.currentRequestAttributes();
			String userAgent = request.getRequest().getHeader("User-Agent");
			//针对IE或者以IE为内核的浏览器：
			if (userAgent.contains("MSIE")||userAgent.contains("Trident")) {
			fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
			} else {
			//非IE浏览器的处理：
			fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
			}
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition","attachment;filename=" + fileName+".xls");
			ServletOutputStream out = response.getOutputStream();
			bis = new BufferedInputStream(is);
			bos = new BufferedOutputStream(out);
			byte[] buff = new byte[2048];
			int bytesRead;
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (bis != null)
				try {
					bis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			if (bos != null)
				try {
					bos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
	}

    public static void export(String templatePath, Map<String, Object> dataMap,
                              String buildFile, String newName,HttpServletRequest request,HttpServletResponse response) {
        try {
            ServletContext application = request.getSession().getServletContext();
            Configuration configuration = new Configuration();
            configuration.setDefaultEncoding("utf-8");
            String path = application.getRealPath("template");
            configuration.setDirectoryForTemplateLoading(new File(path));// 此处是本类Class.getResource()相对于模版文件的相对路径
            Template template = null;
            File outFile = new File(buildFile);
            Writer writer = null;
            template = configuration.getTemplate(templatePath);
            template.setEncoding("utf-8");
            writer = new BufferedWriter(new OutputStreamWriter(
                    new FileOutputStream(outFile), Charset.forName("utf-8")));// 此处为输
            // 出文档编码
            template.process(dataMap, writer);
            writer.flush();
            writer.close();

            // return true;

            // 设置response的编码方式
            response.setContentType("application/x-msdownload");
            // 设置附加文件名
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(newName.getBytes("utf-8"), "iso-8859-1"));

            // 读出文件到i/o流
            FileInputStream fis = new FileInputStream(outFile);
            BufferedInputStream buff = new BufferedInputStream(fis);

            byte[] b = new byte[1024];// 相当于我们的缓存

            long k = 0;// 该值用于计算当前实际下载了多少字节

            // 从response对象中得到输出流,准备下载

            OutputStream myout = response.getOutputStream();

            // 开始循环下载

            while (k < outFile.length()) {

                int j = buff.read(b, 0, 1024);
                k += j;

                // 将b中的数据写到客户端的内存
                myout.write(b, 0, j);

            }

            // 将写入到客户端的内存的数据,刷新到磁盘
            myout.flush();
            myout.close();

        } catch (Exception e) {

            e.printStackTrace();
            // return false;
        }
    }
}
